<?php

/**
 * FootballFactory, Copyright Robin Loop & Pascal Wirz, 2014
 * ---
 * Skript erstellt die Tabellen der Datenbank
 *
 * @author Robin
 */

include($_SERVER['DOCUMENT_ROOT']."/ff_includes/class_database.php");

$sql = <<<sql_statement
  CREATE TABLE IF NOT EXISTS ff_users (
        user_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        user_login TEXT NOT NULL,
        user_password VARCHAR(32) NOT NULL,
        user_nicename TEXT NOT NULL,
        user_email TEXT NOT NULL,
        user_registered DATETIME NOT NULL,
        user_active TINYINT(1) DEFAULT 1,
        user_role BIGINT
    );
    CREATE TABLE IF NOT EXISTS ff_roles (
        role_id BIGINT NOT NULL PRIMARY KEY,
        role_name TEXT NOT NULL,
        role_right TINYINT(1)
    );
    INSERT INTO ff_roles(role_id,role_name,role_right)
        VALUES (
            1,
            "Administrator",
            1
        );   
CREATE TABLE IF NOT EXISTS ff_games (
        game_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        game_hometeam VARCHAR(256) NOT NULL,
        game_awayteam VARCHAR(256) NOT NULL,
        game_date DATETIME NOT NULL,
        game_type BIGINT,
        game_imagelink VARCHAR(256),
        game_reportlink VARCHAR(256),
        game_goalshome BIGINT,
        game_goalsaway BIGINT,
        game_team BIGINT,
        game_season BIGINT NOT NULL,
        game_venue BIGINT NOT NULL
    );
    
CREATE TABLE IF NOT EXISTS ff_gametypes (
        gametype_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        gametype_name VARCHAR(256) NOT NULL,
        gametype_home INT
    );
    
CREATE TABLE IF NOT EXISTS ff_gamevenues (
        gamevenue_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        gamevenue_name VARCHAR(256) NOT NULL,
        gamevenue_city VARCHAR(256),
        gamevenue_maplink VARCHAR(256)
    );
    
CREATE TABLE IF NOT EXISTS ff_seasons (
        season_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        season_name VARCHAR(256) NOT NULL,
        season_startdate DATETIME,
        season_enddate DATETIME
    );
    
CREATE TABLE IF NOT EXISTS ff_scorers (
        scorers_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        player_id BIGINT,
        game_id BIGINT
    );
    
CREATE TABLE IF NOT EXISTS ff_teams (
       team_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
       team_name VARCHAR(256) NOT NULL
   );
CREATE TABLE IF NOT EXISTS ff_players (
       player_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
       player_firstname VARCHAR(256) NOT NULL,
       player_lastname VARCHAR(256) NOT NULL,
       player_team BIGINT NOT NULL,
       player_status INT
   );
CREATE TABLE IF NOT EXISTS ff_seasons_players (
       seasons_players_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
       season_id BIGINT,
       player_id BIGINT
   );
sql_statement;

$db = new Database();
$stmt = $db->prepare($sql);
$stmt->execute();
?>
